﻿<%@ Page Title="Grade Stats" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="GradeStatistics.aspx.cs" Inherits="Testify.GradeStatistics" %>
<%@ MasterType VirtualPath="~/Site.Master" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<div class="container">
    <table>
        <tr>
            <td align="right"><asp:Label ID="lblSection" runat="server" Text="Section:" AssociatedControlID="ddlSection" /></td>
            <td>
                <asp:DropDownList ID="ddlSection" runat="server" DataSourceID="dsSection" DataTextField="section_name" DataValueField="section_id" AutoPostBack="True" />
                <asp:SqlDataSource ID="dsSection" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="
SELECT NULL AS section_id, NULL AS section_name FROM dual
UNION ALL
SELECT s.section_id, ct.course_type_prefix || ' ' || crs.course_number || '-' || s.section_number || ': ' || crs.course_name AS section_name
  FROM section s
       JOIN class cls ON (cls.class_id = s.class_id)
            JOIN course crs ON (crs.course_id = cls.course_id)
                 JOIN course_type ct ON (ct.course_type_id = crs.course_type_id)
 WHERE s.teacher_id = :p_TeacherID
   AND cls.term_id = (SELECT current_term_id FROM environment)
   AND 'D' NOT IN (s.section_maint, cls.class_maint, crs.course_maint, ct.course_type_maint)
 ORDER BY section_name NULLS FIRST, section_id NULLS FIRST">
                    <SelectParameters>
                        <asp:SessionParameter Name="p_TeacherID" SessionField="UserID" />
                    </SelectParameters>
                </asp:SqlDataSource>
            </td>
        </tr>
    </table>

    <asp:GridView ID="grdGradeStats" runat="server" DataSourceID="dsGradeStats" AutoGenerateColumns="False" EmptyDataText="There are no assignments for this section." >
        <Columns>
            <asp:BoundField DataField="test_name" HeaderText="Test" SortExpression="test_name" >
                <ItemStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="completed_count" HeaderText="# of Completed Tests" SortExpression="completed_count" >
                <ItemStyle HorizontalAlign="Right" />
            </asp:BoundField>
            <asp:BoundField DataField="max_score" HeaderText="Highest Score" SortExpression="max_score" >
                <ItemStyle HorizontalAlign="Right" />
            </asp:BoundField>
            <asp:BoundField DataField="avg_score" HeaderText="Average Score" SortExpression="avg_score" >
                <ItemStyle HorizontalAlign="Right" />
            </asp:BoundField>
            <asp:BoundField DataField="min_score" HeaderText="Lowest Score" SortExpression="min_score" >
                <ItemStyle HorizontalAlign="Right" />
            </asp:BoundField>
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="dsGradeStats" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="
SELECT teacher.user_first_name || ' ' || teacher.user_last_name || ': ' || tt.test_type_name || ': ' || t.test_name AS test_name,
       COUNT(gs.score) AS completed_count,
       TO_CHAR(100 * MAX(CASE WHEN gs.score &lt; 0 THEN 0 ELSE gs.score END), 'FM990.00') || '%' AS max_score,
       TO_CHAR(100 * AVG(CASE WHEN gs.score &lt; 0 THEN 0 ELSE gs.score END), 'FM990.00') || '%' AS avg_score,
       TO_CHAR(100 * MIN(CASE WHEN gs.score &lt; 0 THEN 0 ELSE gs.score END), 'FM990.00') || '%' AS min_score
  FROM (SELECT a.test_id,
               r.student_id,
               SUM(r.response_credit) AS total_credit,
               SUM(q.question_weight) -
                 (SELECT SUM(bq.question_weight) AS bonus_total_weight
                    FROM assignment ba
                         JOIN response br ON (br.assignment_id = ba.assignment_id)
                              JOIN question bq ON (bq.question_id = br.question_id)
                   WHERE ba.section_id = :p_SectionID
                     AND NOT EXISTS (SELECT 1
                                       FROM response brr
                                      WHERE brr.student_id    = br.student_id
                                        AND brr.assignment_id = br.assignment_id
                                        AND brr.response_credit IS NULL)
                     AND bq.question_maint != 'D'
                     AND EXISTS (SELECT 1
                                   FROM segment bs
                                  WHERE bs.segment_id       = bq.segment_id
                                    AND bs.segment_is_bonus = 'Y')
                   GROUP BY ba.test_id, br.student_id)
                 AS total_weight,
               NVL(SUM(r.response_credit) / SUM(q.question_weight), 0) AS score
          FROM assignment a
               JOIN response r ON (r.assignment_id = a.assignment_id)
                    JOIN question q ON (q.question_id = r.question_id)
         WHERE a.section_id = :p_SectionID
           AND NOT EXISTS (SELECT 1
                             FROM response rr
                            WHERE rr.student_id    = r.student_id
                              AND rr.assignment_id = r.assignment_id
                              AND rr.response_credit IS NULL)
           AND q.question_maint != 'D'
         GROUP BY a.test_id, r.student_id) gs
       JOIN testify_user student ON (student.user_id = gs.student_id)
       JOIN test t ON (t.test_id = gs.test_id)
            JOIN singularity.test_type tt ON (tt.test_type_id = t.test_type_id)
            JOIN singularity.testify_user teacher ON (teacher.user_id = t.teacher_id)
 WHERE 'D' NOT IN (student.user_maint, tt.test_type_maint, teacher.user_maint)
 GROUP BY teacher.user_first_name || ' ' || teacher.user_last_name || ': ' || tt.test_type_name || ': ' || t.test_name" >
        <SelectParameters>
            <asp:ControlParameter ControlID="ddlSection" Name="p_SectionID" PropertyName="SelectedValue" />
        </SelectParameters>
    </asp:SqlDataSource>
</div>
</asp:Content>